1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmhostelInfo
5 Private Sub auto()
6 Try
7 Dim Num As Integer = 0
8 con = New SqlConnection(cs)
9 con.Open()
10 Dim sql As String = ("SELECT MAX(HI_ID) FROM HostelInfo")
11 cmd = New SqlCommand(sql)
12 cmd.Connection = con
13 If (IsDBNull(cmd.ExecuteScalar)) Then
14 Num = 1
15 txtID.Text = Num.ToString
16 Else
17 Num = cmd.ExecuteScalar + 1
18 txtID.Text = Num.ToString
19 End If
20 cmd.Dispose()
21 con.Close()
22 con.Dispose()
23 Catch ex As Exception
24 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
25 End Try
26 End Sub
27
28 Sub Reset()
29 txtAddress.Text = ""
30 txtPersonIncharge.Text = ""
31 txtContactNo.Text = ""
32 txtContactNo_PI.Text = ""
33 txtID.Text = ""
34 txtHostelName.Text = ""
35 txtHostelName.Focus()
36 btnSave.Enabled = True
37 btnUpdate.Enabled = False
38 btnDelete.Enabled = False
39 auto()
40 End Sub
41 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
42 Me.Close()
43 End Sub
44
45 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
46 If txtHostelName.Text = "" Then
47 MessageBox.Show("Please enter hostel name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
48 txtHostelName.Focus()
49 Return
50 End If
51 If txtAddress.Text = "" Then
52 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
53 txtAddress.Focus()
54 Return
55 End If
56 If txtContactNo.Text = "" Then
57 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
58 txtContactNo.Focus()
59 Return
60 End If
61 Try
62 con = New SqlConnection(cs)
63 con.Open()
64 Dim ct As String = "select HostelName from HostelInfo where Hostelname=@d1"
65 cmd = New SqlCommand(ct)
66 cmd.Parameters.AddWithValue("@d1", txtHostelName.Text)
67 cmd.Connection = con
68 rdr = cmd.ExecuteReader()
69
70 If rdr.Read() Then
71 MessageBox.Show("Hostel Name Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
72 txtHostelName.Text = ""
73 txtHostelName.Focus()
74 If (rdr IsNot Nothing) Then
75 rdr.Close()
76 End If
77 Return
78 End If
79 con = New SqlConnection(cs)
80 con.Open()
81 Dim cb As String = "insert into hostelInfo(HI_Id, Hostelname, Address, ContactNo, ManagedBy, Person_ContactNo) VALUES (@d1,@d2,@d3,@d4,@d5,@d6)"
82 cmd = New SqlCommand(cb)
83 cmd.Connection = con
84 cmd.Parameters.AddWithValue("@d1", txtID.Text)
85 cmd.Parameters.AddWithValue("@d2", txtHostelName.Text)
86 cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
87 cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
88 cmd.Parameters.AddWithValue("@d5", txtPersonIncharge.Text)
89 cmd.Parameters.AddWithValue("@d6", txtContactNo_PI.Text)
90 cmd.ExecuteNonQuery()
91 con.Close()
92 Dim st As String = "added the hostel '" & txtHostelName.Text & "' info"
93 LogFunc(lblUser.Text, st)
94 MessageBox.Show("Successfully saved", "Hostel Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
95 btnSave.Enabled = False
96 Getdata()
97 Catch ex As Exception
98 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
99 End Try
100 End Sub
101
102
103 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
104 If txtHostelName.Text = "" Then
105 MessageBox.Show("Please enter hostel name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
106 txtHostelName.Focus()
107 Return
108 End If
109 If txtAddress.Text = "" Then
110 MessageBox.Show("Please enter address", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
111 txtAddress.Focus()
112 Return
113 End If
114 If txtContactNo.Text = "" Then
115 MessageBox.Show("Please enter contact no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
116 txtContactNo.Focus()
117 Return
118 End If
119 Try
120 con = New SqlConnection(cs)
121 con.Open()
122 Dim cb As String = "Update hostelInfo set hostelName=@d2, Address=@d3, ContactNo=@d4, ManagedBy=@d5, Person_ContactNo=@d6 where HI_ID=@d1"
123 cmd = New SqlCommand(cb)
124 cmd.Connection = con
125 cmd.Parameters.AddWithValue("@d1", txtID.Text)
126 cmd.Parameters.AddWithValue("@d2", txtHostelName.Text)
127 cmd.Parameters.AddWithValue("@d3", txtAddress.Text)
128 cmd.Parameters.AddWithValue("@d4", txtContactNo.Text)
129 cmd.Parameters.AddWithValue("@d5", txtPersonIncharge.Text)
130 cmd.Parameters.AddWithValue("@d6", txtContactNo_PI.Text)
131 cmd.ExecuteNonQuery()
132 con.Close()
133 Dim st As String = "updated the hostel '" & txtHostelName.Text & "' info"
134 LogFunc(lblUser.Text, st)
135 MessageBox.Show("Successfully updated", "Hostel Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
136 btnUpdate.Enabled = False
137 Getdata()
138 Catch ex As Exception
139 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
140 End Try
141 End Sub
142 Public Sub Getdata()
143 Try
144 con = New SqlConnection(cs)
145 con.Open()
146 cmd = New SqlCommand("SELECT RTRIM(HI_Id), RTRIM(hostelName), RTRIM(Address), RTRIM(ContactNo), RTRIM(ManagedBy), RTRIM(Person_ContactNo) from hostelInfo order by hostelname", con)
147 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
148 dgw.Rows.Clear()
149 While (rdr.Read() = True)
150 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5))
151 End While
152 con.Close()
153 Catch ex As Exception
154 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
155 End Try
156 End Sub
157 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
158 Reset()
159 End Sub
160
161
162 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
163 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
164 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
165 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
166 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
167 End If
168 Dim b As Brush = SystemBrushes.ControlText
169 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
170
171 End Sub
172
173 Private Sub frmRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
174 Getdata()
175 End Sub
176
177 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
178 Try
179 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
180 txtID.Text = dr.Cells(0).Value.ToString()
181 txtHostelName.Text = dr.Cells(1).Value.ToString()
182 txtAddress.Text = dr.Cells(2).Value.ToString()
183 txtContactNo.Text = dr.Cells(3).Value.ToString()
184 txtPersonIncharge.Text = dr.Cells(4).Value.ToString()
185 txtContactNo_PI.Text = dr.Cells(5).Value.ToString()
186 btnUpdate.Enabled = True
187 btnSave.Enabled = False
188 btnDelete.Enabled = True
189 Catch ex As Exception
190 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
191 End Try
192 End Sub
193
194 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
195 Try
196 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
197 DeleteRecord()
198 End If
199 Catch ex As Exception
200 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
201 End Try
202 End Sub
203 Private Sub DeleteRecord()
204
205 Try
206 Dim RowsAffected As Integer = 0
207 con = New SqlConnection(cs)
208 con.Open()
209 Dim cl As String = "select HI_ID from hostelInfo,Hosteler where hostelInfo.HI_ID=Hosteler.hostelID and HI_ID=@d1"
210 cmd = New SqlCommand(cl)
211 cmd.Connection = con
212 cmd.Parameters.AddWithValue("@d1", txtID.Text)
213 rdr = cmd.ExecuteReader()
214 If rdr.Read Then
215 MessageBox.Show("Unable to delete..Already in use in Hosteler Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
216 If Not rdr Is Nothing Then
217 rdr.Close()
218 End If
219 Exit Sub
220 End If
221 con = New SqlConnection(cs)
222 con.Open()
223 Dim cl1 As String = "select HI_ID from hostelInfo,Installment_Hostel where hostelInfo.HI_ID=Installment_Hostel.hostelID and HI_ID=@d1"
224 cmd = New SqlCommand(cl1)
225 cmd.Connection = con
226 cmd.Parameters.AddWithValue("@d1", txtID.Text)
227 rdr = cmd.ExecuteReader()
228 If rdr.Read Then
229 MessageBox.Show("Unable to delete..Already in use in Hostel Installment Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
230 If Not rdr Is Nothing Then
231 rdr.Close()
232 End If
233 Exit Sub
234 End If
235 con = New SqlConnection(cs)
236 con.Open()
237 Dim cq As String = "delete from hostelInfo where HI_id=" & txtID.Text & ""
238 cmd = New SqlCommand(cq)
239 cmd.Connection = con
240 RowsAffected = cmd.ExecuteNonQuery()
241 If RowsAffected > 0 Then
242 Dim st As String = "deleted the hostel '" & txtHostelName.Text & "'"
243 LogFunc(lblUser.Text, st)
244 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
245 Getdata()
246 Reset()
247 Else
248 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
249 Reset()
250 End If
251 If con.State = ConnectionState.Open Then
252 con.Close()
253
254 End If
255 Catch ex As Exception
256 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
257 End Try
258 End Sub
259
260 End Class